CREATE DATABASE IF NOT EXISTS natural_foods;
USE natural_foods;

-- Admin Table (Main & Sub Admin)
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('main', 'sub') DEFAULT 'sub',
    status ENUM('active', 'blocked') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default Main Admin (Password: admin123)
INSERT INTO admins (username, password, role) VALUES 
('mainadmin', '$2y$10$8WwL8w6T90x9G4x4x4x4xO1234567890abcdefghijklmnopqrstuvwxyz', 'main')
ON DUPLICATE KEY UPDATE id=id;

-- Products Table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    offer_price DECIMAL(10,2) DEFAULT NULL,
    description TEXT,
    stock_quantity INT DEFAULT 100,
    image VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sliders Table (3 Images)
CREATE TABLE IF NOT EXISTS sliders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    image VARCHAR(255) NOT NULL
);

-- Reviews Table (Image based reviews)
CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    review_image VARCHAR(255) NOT NULL
);

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    contact VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    full_address TEXT NOT NULL,
    district VARCHAR(50) NOT NULL,
    post_code VARCHAR(10) NOT NULL,
    status ENUM('active', 'blocked') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    delivery_charge DECIMAL(10,2) DEFAULT 140.00,
    order_status ENUM('pending', 'confirmed', 'processing', 'shipped', 'return', 'cancle confirmed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);